using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Web.Step04
{
   public static class ProductService
   {
      public static IEnumerable<Product> GetProducts()
      {
         using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TheDatabaseConnectionString"].ConnectionString))
         using (var cmd = new SqlCommand("SELECT * FROM Product", conn))
         {
            conn.Open();

            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while (reader.Read())
               yield return new Product
                               {
                                  Id = (int)reader["Id"],
                                  ProductId = (string)reader["ProductId"],
                                  Description = (string)reader["Description"],
                                  IsActive = (bool)reader["IsActive"]
                               };
         }
      }
   }

   public class Product
   {
      public int Id { get; set; }
      public string ProductId { get; set; }
      public string Description { get; set; }
      public bool IsActive { get; set; }
   }
}